package dao;

import dao.base.BaseDao;
import model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 用户Dao层 查询和批量新增方法使用了hikari连接池
 */
public class UserDao extends BaseDao<User> {
    /**
     * 查询
     * 使用hikari连接池
     */
    @Override
    public List<User> selectAll() {
        Connection conn = getHikariConn();
        List<User> userList = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement("select * from user order by id asc");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Long id = rs.getLong("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                Date createTime = rs.getDate("createTime");
                User user = new User();
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setCreateTime(createTime);

                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return userList;
    }

    /**
     * 新增
     * 使用hikari连接池
     */
    @Override
    public int insert(User user) {
        Connection conn = getHikariConn();
        try {
            PreparedStatement ps = conn.prepareStatement("insert into user(username,password,createTime) values ('张三','123456789',now())");
            int success = ps.executeUpdate();
            return success;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 批量新增
     * 使用hikari连接池
     */
    @Override
    public int batchInsert(List<User> userList) {
        if (userList == null || userList.size() == 0) {
            return 0;
        }
        String sql = "insert into user(id,username,password,createTime)values(?,?,?,?)";
        Connection conn = getHikariConn();
        try {
            //批量新增不自动提交
            conn.setAutoCommit(false);

            PreparedStatement ps = conn.prepareStatement(sql);
            Date now = new Date();
            int size = userList.size();
            for (int i = 0; i < size; i++) {
                User user = userList.get(i);
                ps.setObject(1, user.getId());
                ps.setObject(2, user.getUsername());
                ps.setObject(3, user.getPassword());
                ps.setObject(4, now);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();
            return userList.size();
        } catch (Exception e) {
            try {
                conn.rollback();
                throw new RuntimeException(e);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    /**
     * 修改
     * 根据用户id修改用户的username
     */
    @Override
    public int updateByPrimaryKey(User user) {
        if (user == null || user.getId() == null) {
            return 0;
        }
        Connection conn = getNativeConn();
        try {
            Long id = user.getId();
            String sql = String.format("update user set username = '%s' where id = %s  limit 1", user.getUsername(), id.toString());
            PreparedStatement ps = conn.prepareStatement(sql);
            int success = ps.executeUpdate();
            return success;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 删除
     * 根据用户id删除
     */
    @Override
    public int deleteByPrimaryKey(User user) {
        if (user == null || user.getId() == null) {
            return 0;
        }
        Connection conn = getNativeConn();
        try {
            Long id = user.getId();
            PreparedStatement ps = conn.prepareStatement(String.format("delete from user where id = %s limit 1", id.toString()));
            int success = ps.executeUpdate();
            return success;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public void truncate() {
        Connection conn = getNativeConn();
        try {
            PreparedStatement ps = conn.prepareStatement("truncate user");
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

}
